package com.app.business.service;

import com.app.business.calculate.CalculateUtils;
import com.app.business.en.HouseStatus;
import com.app.business.en.Status;
import com.app.business.model.CarLot;
import com.app.business.model.Contract;
import com.app.business.utils.UserPermissionUtil;
import com.app.system.jpa.JpaBaseDao;
import com.app.system.jpa.PageBean;
import com.app.system.jpa.Wrapper;
import com.app.system.permission.model.DataDict;
import com.app.system.permission.model.UserInfo;
import com.app.system.permission.service.DataDictService;
import com.app.system.permission.service.UserInfoService;
import com.app.system.utils.CollectionUtils;
import com.app.system.utils.ParamUtils;
import com.app.system.utils.WebUtils;
import com.app.system.utils.dataType.BigDecimalUtils;
import com.app.system.utils.dataType.IntegerUtils;
import com.app.system.utils.exception.Precondition;
import com.app.system.utils.exception.Response;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import org.apache.shiro.SecurityUtils;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.Query;
import javax.servlet.http.HttpServletRequest;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.util.*;

/**
 * Created by lsh on 2019/2/11.
 * 车位业务层
 *
 * @author lsh
 */
@SuppressWarnings(value = "ALL")
@Service
public class CarLotService extends JpaBaseDao<CarLot, Integer> {

    @Autowired
    private AzProjectService azProjectService;
    @Autowired
    private HouseService houseService;
    @Autowired
    private ContractService contractService;
    @Autowired
    private UserInfoService userInfoService;
    @Autowired
    private CarLotBuyPersonService carLotBuyPersonService;
    @Autowired
    private UserInfoForPropertyService userInfoForPropertyService;
    @Autowired
    private ActionHistoryService actionHistoryService;
    @Autowired
    private DataDictService dataDictService;

    /**
     * 通过普通户型房源数量得到车位的最大限制数量
     *
     * @param normalHouseNum
     * @return
     */
    public Integer getCarLotLimitCount(Integer normalHouseNum) {
        BigDecimal carLotLimitCount = BigDecimal.ZERO;
        if (IntegerUtils.getNotNull(normalHouseNum) > 0) {
            carLotLimitCount = BigDecimalUtils.getBigAndIntMul(new BigDecimal("0.7"), normalHouseNum).setScale(0, BigDecimal.ROUND_DOWN);
            if (carLotLimitCount.compareTo(BigDecimal.ZERO) == 0) {
                carLotLimitCount = BigDecimal.ONE;
            }
        }
        return Integer.valueOf(String.valueOf(carLotLimitCount));
    }

    /**
     * 车位保存
     *
     * @param request
     * @return
     */
    @Transactional
    public synchronized Response carLotSave(HttpServletRequest request) throws IllegalAccessException, NoSuchMethodException, InvocationTargetException {
        Integer contractId = ParamUtils.getInt(request, "contractId", 0);
        Integer carLotId = ParamUtils.getInt(request, "carLotId", 0);
        Integer sfHg = ParamUtils.getInt(request, "sfHg", 0);
        Contract contract = this.contractService.findOne(contractId);
        CarLot carLot = this.findOne(carLotId);
        Precondition.checkAjaxArguement(contract != null && carLot != null, "1111", "数据错误！");
        Precondition.checkAjaxArguement(contract.getLimitStatus() != null && contract.getLimitStatus().equals(Status.NO.getIndex()), "4444", "该家庭当前暂不能选择车位！");
        Precondition.checkAjaxArguement(contract.getAzSignStatus().equals(Status.TUI.getIndex()) || contract.getAzSignStatus().equals(Status.NO.getIndex()), "2222", "该家庭已经签订安置协议，不允许选择车位！");
        Precondition.checkAjaxArguement(carLot.getHouseStatus().equals(HouseStatus.KEXUAN.getIndex()), "3333", "该车位已被选，请选择其它车位！");
        carLot.setHouseStatus(HouseStatus.YIXUAN.getIndex());   //已选
        carLot.setChooseHouseDate(new Date());   //时间
        carLot.setSfHg(sfHg);//  换购状态
        carLot.setChoosePerson(this.userInfoService.findByUsername(SecurityUtils.getSubject().getPrincipal().toString()).getName());   //操作人
        carLot.setContract(contract);

        //换购所需面积计算
        Map<String, DataDict> carLotTypeMap = this.dataDictService.getMapByParentNameForValue("车位类型");
        String carLotTypeStr = carLotTypeMap.get(carLot.getCarLotType().toString()).getName();
        BigDecimal sfHgArea = BigDecimal.ZERO;
        if (carLot.getSfHg() == 2) {
            sfHgArea = this.carLotZhArea(carLot);
        }
        carLot.setHgArea(sfHgArea);
        this.save(carLot);
        this.actionHistoryService.saveActionHistoryByContractId(contract.getId(), "选择" + carLot.getHouseNumStr(), "选择车位");
        CalculateUtils.main(contract.getId());
        return new Response("0001", "保存成功！");
    }

    /**
     * 车位撤销
     *
     * @param request
     * @return
     */
    @Transactional
    public synchronized Response carLotDelete(HttpServletRequest request) throws IllegalAccessException, NoSuchMethodException, InvocationTargetException {
        Integer carLotId = ParamUtils.getInt(request, "carLotId", 0);
        CarLot carLot = this.findOne(carLotId);
        Contract contract = carLot.getContract();
        Precondition.checkAjaxArguement(carLot != null && contract != null, "1111", "数据错误！");
        Precondition.checkAjaxArguement(contract.getLimitStatus() != null && contract.getLimitStatus().equals(Status.NO.getIndex()), "4444", "该家庭当前暂不能撤销车位！");
        Precondition.checkAjaxArguement(contract.getAzSignStatus().equals(Status.TUI.getIndex()) || contract.getAzSignStatus().equals(Status.NO.getIndex()), "2222", "该家庭已经签订安置协议，不允许撤销车位！");
        Precondition.checkAjaxArguement(carLot.getHouseStatus().equals(HouseStatus.YIXUAN.getIndex()), "3333", "车位已撤销，请勿重复操作！");
        carLot.setHouseStatus(HouseStatus.KEXUAN.getIndex());   //可选
        carLot.setChooseHouseDate(null);   //时间
        carLot.setChoosePerson(null);   //操作人
        carLot.setContract(null);
        carLot.setSfHg(1);//换购状态设置正常购买
        carLot.setHgArea(null);//换购面积
        this.save(carLot);
        this.carLotBuyPersonService.deleteCarLotBuyPersonByCarLotId(carLot.getId());//删除关联的产权人信息
        this.actionHistoryService.saveActionHistoryByContractId(contract.getId(), "撤销" + carLot.getHouseNumStr(), "撤销车位");
        CalculateUtils.main(contract.getId());
        return new Response("0001", "撤销成功！");
    }

    /**
     * 分页处理
     *
     * @return
     */
    public PageBean<CarLot> carLotListByProjectId(Integer projectId) {
        //查询语句
        StringBuffer query = new StringBuffer(" c from CarLot c inner join fetch c.azProject p ");
        //默认排序(PageBean指定排序字段类型的话，则默认排序不使用)
        StringBuffer orderBy = new StringBuffer(" c.buildNum,c.floor,c.houseNum");
        Wrapper wrapper = new Wrapper(query, orderBy);
        wrapper.initSearchParams();
        wrapper.andEq("p.id", projectId);
        return this.pageHql(wrapper);
    }


    /**
     * 分页处理
     *
     * @return
     */
    public PageBean<CarLot> carLotExcelListByProjectId(Integer projectId) {
        //查询语句
        StringBuffer query = new StringBuffer(" c from CarLot c inner join fetch c.azProject p ");

        //默认排序(PageBean指定排序字段类型的话，则默认排序不使用)
        StringBuffer orderBy = new StringBuffer(" p.id,c.buildNum,c.houseNum");
        PageBean pageBean = new PageBean();
        pageBean.setPageSize(Integer.MAX_VALUE);
        Wrapper wrapper = new Wrapper(pageBean, query, null, orderBy);
        wrapper.initSearchParams();
        wrapper.andEq("p.id", projectId);
        return this.pageHql(wrapper);
    }

    /**
     * 分页处理（根据征收项目，得到所分配的安置项目以及车位）
     *
     * @return
     */
    public PageBean<CarLot> getCarLotPageByZsProjectId(Integer zsProjectId) {
        List<String> azProjectIdList = this.houseService.getAzProjectIdListByZsProjectId(zsProjectId);
        //如果查出是空，存入一个id0，防止in查询报错
        if (CollectionUtils.isEmpty(azProjectIdList)) {
            azProjectIdList.add("0");
        }
        //查询语句
        StringBuffer query = new StringBuffer(" c from CarLot c inner join fetch c.azProject ap ");
        //默认排序(PageBean指定排序字段类型的话，则默认排序不使用)
        StringBuffer orderBy = new StringBuffer(" ap.id,c.houseStatus,c.buildNum,c.floor,c.houseNum");
        Wrapper wrapper = new Wrapper(query, orderBy);
        wrapper.initSearchParams();
        wrapper.andIn("ap.id", azProjectIdList);
        return this.pageHql(wrapper);
    }

    /**
     * 所有安置项目得到车位数量(总计，已选)
     *
     * @param azProjectId
     * @return
     */
    public Map<String, Map> getCarLotCountMap() {
        StringBuffer sql = new StringBuffer("select c.az_project_id as azProjectId,ifnull(COUNT(c.id),0) as carLotTotal,");
        sql.append("ifnull(sum(case when c.house_status = 2  then 1 else 0 end),0) as signCarLot,");
        sql.append("ifnull(sum(case when c.take_house_status = 2  then 1 else 0 end),0) as takeCarLotCount ");
        sql.append(" from car_lot c group by c.az_project_id order by c.az_project_id");
        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map> carLotCountMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (Map map : mapList) {
                String azProjectId = map.get("azProjectId").toString();
                carLotCountMap.put(azProjectId, map);
            }
        }
        return carLotCountMap;
    }

    /**
     * 根据协议id获取已选择的车位list
     *
     * @param azProjectId
     * @return
     */
    public List<CarLot> getCarLotListByContractId(Integer contractId) {
        String hql = " from CarLot c inner join fetch c.azProject ap where c.contract.id = :contractId order by c.chooseHouseDate";
        Map<String, Object> map = new HashMap<>();
        map.put("contractId", contractId);
        List<CarLot> carLotList = this.findHql(hql, map);
        return carLotList;
    }

    /**
     * 根据协议id获取已选择的期房（地块三）车位list
     *
     * @param azProjectId
     * @return
     */
    public List<CarLot> getCarLotListByContractIdAndHouseNature(Integer contractId,Integer houseNature) {
        String hql = " from CarLot c inner join fetch c.azProject ap where c.contract.id = :contractId and c.houseNature= :houseNature order by c.chooseHouseDate";
        Map<String, Object> map = new HashMap<>();
        map.put("contractId", contractId);
        map.put("houseNature", houseNature);
        List<CarLot> carLotList = this.findHql(hql, map);
        return carLotList;
    }

    /**
     * 非人防信息列表
     * @param contractId
     * @param noRfList
     * @return
     */
    public List<CarLot> getNoRfCarLotListByContractId(Integer contractId,String noRfList) {
        String hql = " from CarLot c inner join fetch c.azProject ap where c.contract.id = :contractId and c.carLotType in "+ noRfList +" order by c.chooseHouseDate";
        Map<String, Object> map = new HashMap<>();
        map.put("contractId", contractId);
        List<CarLot> carLotList = this.findHql(hql, map);
        return carLotList;
    }


    /**
     * 根据协议id获取已选择的车位list
     *
     * @param azProjectId
     * @return
     */
    public List<CarLot> getHandInMoneyCarLotListByContractId(Integer contractId) {
        String hql = " from CarLot c inner join fetch c.azProject ap where c.contract.id = :contractId  and c.financeSettleStatus = 2 order by c.chooseHouseDate";
        Map<String, Object> map = new HashMap<>();
        map.put("contractId", contractId);
        List<CarLot> carLotList = this.findHql(hql, map);
        return carLotList;
    }

    /**
     * 通过f房源idList获取某些房源
     *
     * @param houseIdList
     * @return
     */
    public List<CarLot> getCarLotListByIds(String carLotIdStr) {
        StringBuffer hql = new StringBuffer("from CarLot c inner join fetch c.azProject azp where c.id in (" + carLotIdStr + ") order by c.chooseHouseDate");
        List<CarLot> carLotList = entityManager.createQuery(hql.toString()).getResultList();
        return carLotList;
    }

    /**
     * 通过安置项目id获取所有 具有唯一性的 车位信息
     * Set<安置项目id-幢号-户号-所在层数>
     *
     * @param azProjectId
     * @return
     */
    public Set<String> findCarLotSetByAzProjectId(Integer azProjectId) {
        StringBuffer hql = new StringBuffer("select CONCAT(c.az_project_id,'-',c.build_num,'-',c.house_num,'-',c.floor) ");
        hql.append(" from Car_Lot c where c.az_project_id =:azProjectId ");
        Map<String, Object> map = Maps.newLinkedHashMap();
        map.put("azProjectId", azProjectId);

        List<String> list = this.findSqlObject(hql.toString(), map);
        Set<String> resultSet = Sets.newHashSet();
        if (CollectionUtils.isNotEmpty(list)) {
            for (String carLotString : list) {
                resultSet.add(carLotString);
            }
        }
        return resultSet;
    }

    /**
     * 已选车位明细分页处理
     *
     * @return
     */
    public PageBean<CarLot> getSelectedCarLotPageBean() {
        //查询语句
        StringBuffer query = new StringBuffer("cl from CarLot cl inner join fetch cl.contract c inner join fetch c.zsProject zsp  inner join fetch cl.azProject azp");

        //默认排序(PageBean指定排序字段类型的话，则默认排序不使用)
        StringBuffer orderBy = new StringBuffer("zsp.id,c.id,cl.id");
        Wrapper wrapper = new Wrapper(query, orderBy);
        wrapper.initSearchParams();
        wrapper.andEq("cl.houseStatus", Status.YES.getIndex());

        UserPermissionUtil.zsProjectUserLimit(wrapper);        //用户只能看到已经分配给自己的项目下的协议信息

        return this.pageHql(wrapper);
    }

    /**
     * 已选车位明细导出，不分页
     *
     * @return
     */
    public PageBean<CarLot> getExcelSelectedCarLotPageBean() {
        //查询语句
        StringBuffer query = new StringBuffer("cl from CarLot cl inner join fetch cl.contract c inner join fetch c.zsProject zsp  inner join fetch cl.azProject azp");

        //默认排序(PageBean指定排序字段类型的话，则默认排序不使用)
        StringBuffer orderBy = new StringBuffer("zsp.id,c.id,cl.id");
        PageBean pageBean = new PageBean();
        pageBean.setPageSize(Integer.MAX_VALUE);
        Wrapper wrapper = new Wrapper(pageBean, query, null, orderBy);
        wrapper.andEq("cl.houseStatus", Status.YES.getIndex());

        wrapper.initSearchParams();
        UserPermissionUtil.zsProjectUserLimit(wrapper);        //用户只能看到已经分配给自己的项目下的协议信息

        return this.pageHql(wrapper);
    }


    /**
     * 档案已发放车位明细分页处理
     * 交房列表
     *
     * @return
     */
    public PageBean<CarLot> getDaffCarLotPageBean() {
        UserInfo userInfo = WebUtils.getLoginUserInfo();
        List<Integer> azProjectIdList = this.userInfoForPropertyService.findAzProjectIdByUserIdAndTowm(userInfo.getId());
        azProjectIdList.add(0);
        //查询语句
        StringBuffer query = new StringBuffer("cl from CarLot cl inner join fetch  cl.azProject azp inner join fetch cl.contract c ");
        StringBuffer orderBy = new StringBuffer("cl.takeHouseStatus,azp.id,cl.buildNum,cl.houseNum");
        Wrapper wrapper = new Wrapper(query, orderBy);
        wrapper.initSearchParams();
        wrapper.andEq("cl.houseStatus", Status.YES.getIndex());
        wrapper.andIn("azp.id", azProjectIdList);
        return this.pageHql(wrapper);
    }

    /**
     * 档案已发放车位明细分页处理导出
     * 物业列表
     *
     * @return
     */
    public PageBean<CarLot> getDaffCarLotExcelPageBean() {
        UserInfo userInfo = WebUtils.getLoginUserInfo();
        List<Integer> azProjectIdList = this.userInfoForPropertyService.findAzProjectIdByUserIdAndTowm(userInfo.getId());
        azProjectIdList.add(0);
        //查询语句
        StringBuffer query = new StringBuffer("cl from CarLot cl inner join fetch  cl.azProject azp inner join fetch cl.contract c ");
        StringBuffer orderBy = new StringBuffer("cl.takeHouseStatus,azp.id,cl.buildNum,cl.houseNum");
        PageBean pageBean = new PageBean();
        pageBean.setPageSize(Integer.MAX_VALUE);
        Wrapper wrapper = new Wrapper(pageBean, query, null, orderBy);
        wrapper.initSearchParams();
        wrapper.andEq("cl.houseStatus", Status.YES.getIndex());
        wrapper.andIn("azp.id", azProjectIdList);
        return this.pageHql(wrapper);
    }

    /**
     * 得到协议id不为空的车位map
     *
     * @return
     */
    public Map<String, List<CarLot>> getHouseByContractIdMap() {
        StringBuffer hql = new StringBuffer("from CarLot c inner join fetch c.azProject azp where c.contract.id is not null order by c.chooseHouseDate");
        List<CarLot> carLotList = entityManager.createQuery(hql.toString()).getResultList();

        List<CarLot> valueList = null;

        Map<String, List<CarLot>> map = Maps.newLinkedHashMap();
        if (CollectionUtils.isNotEmpty(carLotList)) {
            for (CarLot c : carLotList) {
                String id = String.valueOf(c.getContract().getId());
                valueList = map.get(id) == null ? new ArrayList<>() : map.get(id);
                valueList.add(c);
                map.put(id, valueList);
            }
        }
        return map;
    }

    /**
     * 得到某些协议所对应的车位，并封装map
     *
     * @param contractIdList
     * @return
     */
    public Map<Integer, List<CarLot>> getCarLotMapByContractIdList(List<Integer> contractIdList) {
        StringBuffer hql = new StringBuffer("from CarLot cl inner join fetch cl.contract c where c.id in (:contractIdList) order by cl.buildNum,cl.houseNum ");
        List<CarLot> carLotList = entityManager.createQuery(hql.toString()).setParameter("contractIdList", contractIdList).getResultList();
        Map<Integer, List<CarLot>> resultMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(carLotList)) {
            List<CarLot> itemList = Lists.newLinkedList();
            for (CarLot carLot : carLotList) {
                itemList = null;
                itemList = resultMap.get(carLot.getContract().getId()) == null ? Lists.newLinkedList() : resultMap.get(carLot.getContract().getId());
                itemList.add(carLot);
                resultMap.put(carLot.getContract().getId(), itemList);
            }
        }
        return resultMap;
    }

    /**
     * 设置车位结算状态（可撤销）
     *
     * @param house
     * @return
     */
    @Transactional
    public void setCarLotFinanceSettleStatus(CarLot carLot) throws IllegalAccessException, NoSuchMethodException, InvocationTargetException {
        if (carLot.getFinanceSettleStatus().equals(Status.NO.getIndex())) {
            carLot.setFinanceSettleStatus(Status.YES.getIndex());
            carLot.setFinanceSettleDate(new Date());
        } else {
            carLot.setFinanceSettleStatus(Status.NO.getIndex());
            carLot.setFinanceSettleDate(null);
        }
        this.save(carLot);
    }

    /**
     * 根据协议id和状态获取已选择的车位list
     *
     * @param contractId
     * @param status
     * @return
     */
    public List<CarLot> getCarLotListByContractIdAndStatus(Integer contractId, String status) {

        Map<String, Object> map = Maps.newLinkedHashMap();
        StringBuffer sql = new StringBuffer(" from CarLot c inner join fetch c.azProject ap where c.contract.id = :contractId  ");
        map.put("contractId", contractId);
        if (status.equals("houseStatus")) {
            sql.append(" order by c.houseStatus desc,c.chooseHouseDate");
        } else if (status.equals("financeSettleStatus")) {
            sql.append(" order by c.financeSettleStatus desc,c.financeSettleDate");
        } else if (status.equals("takeHouseStatus")) {
            sql.append(" order by c.takeHouseStatus desc,c.takeHouseDate");
        } else if (status.equals("bzStatus")) {
            sql.append(" order by c.bzStatus desc,c.bzDate");
        }
        return this.findHql(sql.toString(), map);
    }


    /**
     * 得到安置名称下所协议id
     *
     * @param azProjectName
     * @return
     */
    public List<Integer> getContractIdListByAzProjectName(String azProjectName, String startDate, String stopDate) {
        String sql = "select cl.contract_id  from car_lot cl inner join az_project azp on cl.az_project_id=azp.id ";
        sql = sql + " where cl.contract_id is not null ";
        if (azProjectName != null && !azProjectName.equals("")) {
            sql = sql + " and azp.project_name like  '%" + azProjectName + "%'";
        }
        if (startDate != null && startDate != "") {
            sql = sql + " and DATE(cl.choose_house_date) >= " + "'" + startDate + "'";
        }
        if (stopDate != null && stopDate != "") {
            sql = sql + " and DATE(cl.choose_house_date) <=" + "'" + stopDate + "'";
        }
        return this.entityManager.createNativeQuery(sql)
                .getResultList();
    }
    /**
     * 得到某一年内
     *
     * @param azProjectName
     * @return
     */
    public List<Integer> getCarLotIdListByChooseHouseDateYear(String yearStr) {
        String sql = "select  cl.id  from car_lot cl  ";
        sql = sql + " where cl.choose_house_date is not null ";
        if (yearStr != null && !yearStr.equals("")) {
            sql = sql + " and YEAR(cl.choose_house_date) = " + yearStr;
        }
        return this.entityManager.createNativeQuery(sql)
                .getResultList();
    }

    /**
     * 协议下房屋审核情况list
     * @param contractId
     * @return
     */
    public List<String> getCarLotDeedShStatusList(Integer contractId) {
        StringBuffer sql = new StringBuffer("select distinct c.deed_sh_status as deedShStatus from car_lot c ");
        sql.append(" where c.contract_id =:contractId ");
        List<String> getHouseDeedShStatusList = (List<String>) this.entityManager.createNativeQuery(sql.toString()).setParameter("contractId", contractId).getResultList();
        return getHouseDeedShStatusList;
    }

    /**
     * 车位换购时得到对应的换购面积
     * @param carLot
     * @return
     */
    public BigDecimal carLotZhArea(CarLot carLot) {
        //换购所需面积计算
        Map<String, DataDict> carLotTypeMap = this.dataDictService.getMapByParentNameForValue("车位类型");
        String carLotTypeStr = carLotTypeMap.get(carLot.getCarLotType().toString()).getName();
        BigDecimal sfHgArea = BigDecimal.ZERO;
        if (carLot.getSfHg() == 2) {
            if (carLotTypeStr.contains("子母车位")) {
                sfHgArea = new BigDecimal("18");//字母车位换购 字母车位也只用18 只不过需补子车位所需价格
            } else {
                sfHgArea = new BigDecimal("18");//非子母车位换购
            }
        }
        return sfHgArea;
    }

    /**
     * 通过安置项目、得到所分配的carlot的类型情况
     *
     * @param zsProjectId
     * @param azProjectId
     * @return
     */
    public Map<String, Map<String, Map>> getCarlotTypeForHQMap(String azProjectStr, String houseStyle){
        Map<String, Map<String, Map>> carLotTypeMap = Maps.newLinkedHashMap();
        StringBuffer sql = new StringBuffer("select c.az_project_id as azProjectId,ap.project_name,c.car_lot_type as carLotType, ");
        sql.append("ifnull(count(c.id),0) as carLotTotal,sum(ifnull(c.build_area,0)) as carLotAreaTotal, ");
        sql.append("sum(case when c.house_status !=1 then 1 else 0 end) as chooseCarLotTotal, ");
        sql.append("sum(case when c.house_status !=1 then (ifnull(c.build_area,0)) else 0 end) as chooseCarLotAreaTotal, ");
        sql.append("sum(case when c.house_status =1 then 1 else 0 end) as unChooseCarLotTotal, ");
        sql.append("sum(case when c.house_status =1 then (ifnull(c.build_area,0)) else 0 end) as unChooseCarLotAreaTotal ");
        sql.append("from car_lot c left join az_project ap on c.az_project_id = ap.id ");
        sql.append("where c.az_project_id in (" + azProjectStr + ") ");
        sql.append("GROUP BY c.az_project_id,c.car_lot_type ");
        sql.append("order by c.az_project_id,c.car_lot_type");
        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        if (CollectionUtils.isNotEmpty(mapList)) {
            Map<String, Map> itemMap = Maps.newLinkedHashMap();
            for (Map map : mapList) {
                String azProjectId = map.get("azProjectId").toString();
                String carLotType = map.get("carLotType").toString();

                itemMap = carLotTypeMap.get(azProjectId) == null ? Maps.newLinkedHashMap() : carLotTypeMap.get(azProjectId);
                itemMap.put(carLotType, map);
                carLotTypeMap.put(azProjectId, itemMap);
            }
        }
        return carLotTypeMap;
    }



}
